package com.viking.util.database.query;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class QueryInformationDataBase {
    private static ResultSet resultSet;
    private static int Max;



    public static String[][] PatientInformation(int start, int limit) throws Exception  {
        Class.forName ("com.mysql.cj.jdbc.Driver");
//        2.用户信息和url
        String url = "jdbc:mysql://8.130.32.3:3306/testing?useUnicode=true&characterEncoding=utf-8";
        String username="testing";
        String password="Yiee4M4sHAaNXAMf";
//        3.连接成功，数据库对象 Connection
        Connection connection = DriverManager.getConnection(url,username,password);
//        4.执行SQL对象Statement，执行SQL的对象

        Statement stmt = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        resultSet= stmt.executeQuery("SELECT\n" +
                "\tpatientinformation.*\n" +
                "FROM\n" +
                "\tpatientinformation\n" +
                "LIMIT "+start+","+limit);
        String[][] Data=new String[limit][26];
        int i=0;
        while(resultSet.next())
        {
            Data[i][0] = resultSet.getString(1);
            Data[i][1] = resultSet.getString(2);
            Data[i][2] = resultSet.getString(3);
            Data[i][3] = resultSet.getString(4);
            Data[i][4] = resultSet.getString(5);
            Data[i][5] = resultSet.getString(6);
            Data[i][6] = resultSet.getString(7);
           i++;
        }
        for(i=0;i<limit;i++){
            System.out.print(  Data[i][0]+"        ");
            System.out.print(  Data[i][1]+"        ");
            System.out.print(  Data[i][2]+"        ");
            System.out.print(  Data[i][3]+"        ");
            System.out.print(  Data[i][4]+"        ");
            System.out.print(  Data[i][5]+"        ");
            System.out.print(  Data[i][6]+"        ");

            System.out.println("");
        }
        resultSet.close();
        return Data;


    }

    public static int getMax() throws Exception {
        Class.forName ("com.mysql.cj.jdbc.Driver");
//        2.用户信息和url
        String url = "jdbc:mysql://8.130.32.3:3306/testing?useUnicode=true&characterEncoding=utf-8";
        String username="testing";
        String password="Yiee4M4sHAaNXAMf";
//        3.连接成功，数据库对象 Connection
        Connection connection = DriverManager.getConnection(url,username,password);
//        4.执行SQL对象Statement，执行SQL的对象

        Statement stmt = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        resultSet= stmt.executeQuery("SELECT COUNT('*') FROM patientinformation");
        while(resultSet.next())
        {
            Max=resultSet.getInt(1);
        }
        resultSet.close();
        connection.close();
        return Max;
    }
    public static String[][] PatientId(int id)throws Exception
    {
        resultSet.absolute(1);
        String res[][] =new String[getMax()][7];
        int i=0;
        while (resultSet.next())
        {
            System.out.println(resultSet.getString(1));
            if(resultSet.getString(1) == String.valueOf(id)){

               res[i][0] = resultSet.getString(1);
               res[i][1] = resultSet.getString(2);
               res[i][2] = resultSet.getString(3);
               res[i][3] = resultSet.getString(4);
               res[i][4] = resultSet.getString(5);
               res[i][5] = resultSet.getString(6);
               res[i][6] = resultSet.getString(7);
                i++;
            }

        }
        int a=0;
        for( a=0;a<=i;a++)
        {
            System.out.print(res[a][0]+"     ");
            System.out.print(res[a][1]+"     ");
            System.out.print(res[a][2]+"    ");
            System.out.print(res[a][3]+"    ");
            System.out.print(res[a][4]+"     ");
            System.out.print(res[a][5]+"     ");
            System.out.print(res[a][6]+"    ");
            System.out.println("\n");
        }
        return res;
    }
    //查询患者名字
    public static String[][] PatientId(String id1) throws Exception {
        Class.forName ("com.mysql.cj.jdbc.Driver");
//        2.用户信息和url
        String url = "jdbc:mysql://8.130.32.3:3306/testing?useUnicode=true&characterEncoding=utf-8";
        String username="testing";
        String password="Yiee4M4sHAaNXAMf";
//        3.连接成功，数据库对象 Connection
        Connection connection = DriverManager.getConnection(url,username,password);
//        4.执行SQL对象Statement，执行SQL的对象
        Statement statement = connection.createStatement();
//        5.执行SQL的对象去执行SQL，返回结果集
         /*   String sql = "SELECT\n" +
                    "\tpatientinformation.*\n" +
                    "FROM\n" +
                    "\tpatientinformation";
     */
        int id = Integer.parseInt(id1);
        Statement stmt = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet= stmt.executeQuery("SELECT" +
                "\t*\n" +
                "FROM\n" +
                "\tpatientinformation\n" +
                "WHERE\n" +
                "\tpatientinformation.`患者编号` = "+id);

        String [][]data = new String[getMax()][7];

        int i=0;
        while(resultSet.next())
        {
            System.out.println(resultSet.getString(1));
            data[i][0] =resultSet.getString(1);
            data[i][1] =resultSet.getString(2);
            data[i][2] =resultSet.getString(3);
            data[i][3] =resultSet.getString(4);
            data[i][4] =resultSet.getString(5);
            data[i][5] =resultSet.getString(6);
            data[i][6] =resultSet.getString(7);
            i++;
        }
        return data;
    }
    public static String[][] PatienName(String name)throws Exception{
        Class.forName ("com.mysql.cj.jdbc.Driver");
//        2.用户信息和url
        String url = "jdbc:mysql://8.130.32.3:3306/testing?useUnicode=true&characterEncoding=utf-8";
        String username="testing";
        String password="Yiee4M4sHAaNXAMf";
//        3.连接成功，数据库对象 Connection
        Connection connection = DriverManager.getConnection(url,username,password);
//        4.执行SQL对象Statement，执行SQL的对象
        Statement statement = connection.createStatement();
//        5.执行SQL的对象去执行SQL，返回结果集
         /*   String sql = "SELECT\n" +
                    "\tpatientinformation.*\n" +
                    "FROM\n" +
                    "\tpatientinformation";
     */
        Statement stmt = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet= stmt.executeQuery("SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\tpatientinformation\n" +
                "WHERE\n" +
                "\tpatientinformation.`患者名` = \"" +  name + "\"");

        String [][]data = new String[getMax()][7];
        int i=0;
        while(resultSet.next())
        {
            System.out.println(resultSet.getString(1));
            data[i][0] =resultSet.getString(1);
            data[i][1] =resultSet.getString(2);
            data[i][2] =resultSet.getString(3);
            data[i][3] =resultSet.getString(4);
            data[i][4] =resultSet.getString(5);
            data[i][5] =resultSet.getString(6);
            data[i][6] =resultSet.getString(7);
            i++;
        }
        return data;
    }
}
